"""step5_0_create_core_views

根据 env_structure.xlsx 生成五个基础视图：v_setl, v_mdtrt, v_fee, v_dx, v_tx。
规则：
- 仅涉及逻辑表名 SETL / MDTRT / FEE / DX / TX。
- 每个视图字段集合：env_structure 中 tbl_name=该逻辑表 的字段；
- 物理来源列永远使用 field_code；core 仅作为“视图中的别名”（如果非空），否则别名仍为 field_code。
- 忽略在物理表中不存在的 field_code 列（可选：打印告警）。
- 物理表真实名称通过环境变量：SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME；缺省则使用逻辑表名本身。

执行：python STEP5干净表/step5_0_create_core_views.py

后续再单独生成 *_map 视图。
"""
from __future__ import annotations
import os
import sys
import pandas as pd
from sqlalchemy import text
from sqlalchemy.engine import Engine

ROOT_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), '..'))
if ROOT_DIR not in sys.path:
    sys.path.append(ROOT_DIR)
from config import create_db_engine, load_env_tbl_name

STRUCTURE_FILE = 'env.xlsx'
STRUCTURE_SHEET = 'env_structure'
LOGIC_TABLES = ['SETL', 'MDTRT', 'FEE', 'DX', 'TX']

# 获得所有表名
SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME = load_env_tbl_name()

ENV_VAR_MAP = {
    'SETL': SETL_TBL_NAME,
    'MDTRT': MDTRT_TBL_NAME,
    'FEE': FEE_TBL_NAME,
    'DX': DX_TBL_NAME,
    'TX': TX_TBL_NAME,
}

def resolve_phys(logic_name: str) -> str:
    env_var = ENV_VAR_MAP.get(logic_name.upper())
    return env_var

def load_structure() -> pd.DataFrame:
    df = pd.read_excel(STRUCTURE_FILE, sheet_name=STRUCTURE_SHEET)
    needed = ['tbl_name', 'field_code', 'core']
    for c in needed:
        if c not in df.columns:
            raise ValueError(f'env_structure.xlsx 缺少列: {c}')
    df['tbl_name'] = df['tbl_name'].str.upper()
    df['field_code'] = df['field_code'].str.upper()
    df['core'] = df['core'].fillna('').astype(str).str.strip().str.upper()
    return df[df['tbl_name'].isin(LOGIC_TABLES)].copy()


def column_exists(engine: Engine, table_name: str, column_name: str) -> bool:
    sql = text('SELECT 1 FROM user_tab_columns WHERE table_name = :t AND column_name = :c')
    with engine.connect() as conn:
        r = conn.execute(sql, {'t': table_name.upper(), 'c': column_name.upper()}).fetchone()
        return r is not None


def build_view_sql(engine: Engine, logic_table: str, df_sub: pd.DataFrame) -> str:
    phys = resolve_phys(logic_table)
    select_items = []
    missing = []
    for row in df_sub.itertuples(index=False):
        source_col = row.field_code  # 物理列固定为 field_code
        alias_col = row.core if row.core else row.field_code  # 输出别名：core 优先
        if not column_exists(engine, phys, source_col):
            missing.append(source_col)
            continue
        # 若别名与来源相同可省 AS，保持统一写法便于审阅
        select_items.append(f"{source_col} AS {alias_col}")
    if not select_items:
        raise RuntimeError(f"{logic_table} 无可用字段，检查结构配置或物理表。")
    sel = ',\n       '.join(select_items)
    return f"CREATE OR REPLACE VIEW V_{logic_table.lower()} AS\nSELECT {sel}\nFROM {phys}" , missing


def create_views(engine: Engine, struct_df: pd.DataFrame):
    for logic in LOGIC_TABLES:
        df_sub = struct_df[struct_df['tbl_name'] == logic]
        if df_sub.empty:
            print(f"[WARN] {logic} 在结构表中无字段，跳过。")
            continue
        sql, missing = build_view_sql(engine, logic, df_sub)
        with engine.begin() as conn:
            conn.execute(text(sql))
        print(f"[VIEW] V_{logic.lower()} 创建完成, 字段数={len(df_sub)-len(missing)}, 缺失(物理不存在)={len(missing)}")
        if missing:
            print(f"  -> 缺失列: {', '.join(missing[:10])}{' ...' if len(missing)>10 else ''}")


def main():
    engine = create_db_engine()
    struct_df = load_structure()
    create_views(engine, struct_df)
    print('[DONE] 基础视图全部生成完成')


if __name__ == '__main__':
    main()
